﻿Public Class drugItemClass
    Dim connect As CONDB = CONDB.NewConnection()

    Private DRGCODE = Nothing
    Private PRDCODE = Nothing
    Private GDCODE = Nothing
    Private DACCCODE = Nothing
    Private ATCL1CODE = Nothing
    Private ATCL2CODE = Nothing
    Private ATCL3CODE = Nothing
    Private ATCL4CODE = Nothing
    Private ATCL5CODE = Nothing
    Private ATC = Nothing
    Private DFORM = Nothing
    Private DGTYID = Nothing
    Private INTEN = Nothing
    Private UGID = Nothing
    Private DRGMID = Nothing
    Private DRGLIST = Nothing
    Private GENERICNAME = Nothing
    Private TRADENAME = Nothing
    Private DLABEL = Nothing
    Private REMARK = Nothing
    Private KEEPID = Nothing
    Private LOCATION = Nothing
    Private MAXLV = Nothing
    Private MINLV = Nothing
    Private AVGUSE = Nothing
    Private REORDER = Nothing
    Private KWORD = Nothing
    Private STATUS = Nothing
    Private B_UNIT = Nothing
    Private S_UNIT = Nothing
    Private U_UNIT = Nothing
    Private F_MS = Nothing
    Private selectDrugitem = "SELECT a.drgcode AS 'รหัสยา', a.prdcode AS 'รหัสผลิตภัณฑ์', a.gdcode AS 'กลุ่มยา', c.grpdrg AS 'ชื่อกลุ่มยา', a.dacccode AS 'รหัสบัญชียา', d.daccname AS 'บัญชียา', a.atcl1code AS 'รหัส class ยา 1', e.al1name AS 'class ยา 1', a.atcl2code AS 'รหัส class ยา 2', f.al2name AS 'class ยา 2', a.atcl3code AS 'รหัส class ยา 3', g.al3name AS 'class ยา 3', a.atcl4code AS 'รหัส class ยา 4', h.al4name AS 'class ยา 4', a.atcl5code AS 'รหัส class ยา 5', i.al5name AS 'class ยา 5', a.atc AS 'รหัส class ยา ATC', a.dform AS 'รหัสรูปแบบยา', j.dosageform AS 'รูปแบบยา', a.dgtyid AS 'รหัสประเภทยา', k.drgtype AS 'ประเภทยา', a.inten AS 'รหัสความรุนแรง', l.intensity AS 'ความรุนแรง', a.ugid AS 'รหัสการใช้ยา', s.ugname AS 'การใช้ยา', a.drgmid AS 'รหัสผู้ผลิต', m.makername AS 'ผู้ผลิต', a.drglist AS 'บัญชียาหลัก', a.genericname AS 'ชื่อสามัญทางยา', a.tradename AS 'ชื่อทางการค้า', a.dlabel AS 'ฉลากยา', a.remark AS 'หมายเหตุ', a.keepid AS 'วิธีการเก็บ', n.dkeepname AS 'ชื่อวิธีการเก็บ', a.location AS 'สถานที่เก็บยา', o.locatename AS 'ชื่อสถานที่เก็บยา', a.maxlv AS 'ปริมาณสูงสุด', a.minlv AS 'ปริมาณต่ำสุด', a.avguse AS 'อัตราเฉลี่ยการใช้', a.reorder AS 'ปริมาณถึงจุดสั่งซื้อ', a.`status` AS 'สถานะการเปิดใช้งาน', a.kword AS 'คำค้นหา', b.b_unit AS 'หน่วยซื้อ', p.unitname_th AS 'ชื่อหน่วยซื้อ', b.s_unit AS 'หน่วยขาย', q.unitname_th AS 'ชื่อหน่วยขาย', b.u_unit AS 'หน่วยใช้', r.unitname_th AS 'ชื่อหน่วยใช้', a.`f_ms` as 'รหัสประเภทเวชภัณฑ์', CASE f_ms WHEN '1' THEN 'เวชภัณฑ์' WHEN '0' THEN 'ยา' ELSE '' END AS ประเภทเวชภัณฑ์  FROM drugitem a JOIN masproduct b ON a.prdcode = b.prdcode LEFT JOIN masgrpdrg c ON a.gdcode = c.gdcode LEFT JOIN masdrgacc d ON a.dacccode = d.dacccode LEFT JOIN masdrgatcl1 e ON a.atcl1code = e.atcl1code LEFT JOIN masdrgatcl2 f ON a.atcl2code = f.atcl2code LEFT JOIN masdrgatcl3 g ON a.atcl3code = g.atcl3code LEFT JOIN masdrgatcl4 h ON a.atcl4code = h.atcl4code LEFT JOIN masdrgatcl5 i ON a.atcl5code = i.atcl5code LEFT JOIN masdrgdosage j ON a.dform = j.dform LEFT JOIN masdrgtype k ON a.dgtyid = k.dgtyid LEFT JOIN masdrginten l ON a.inten = l.inten LEFT JOIN masdrgmaker m ON a.drgmid = m.drgmid LEFT JOIN masdrgkeep n ON a.keepid = n.keepid LEFT JOIN maslocationd o ON a.location = o.location LEFT JOIN masunit p ON b.b_unit = p.unitid LEFT JOIN masunit q ON b.s_unit = q.unitid LEFT JOIN masunit r ON b.u_unit = r.unitid LEFT JOIN masdrgug s ON a.ugid = s.ugid ORDER BY a.drgcode ASC"
    'Private selectDrugitem = "SELECT a.DRGCODE AS 'รหัสยา', a.PRDCODE AS 'รหัสสินค้า', a.GDCODE AS 'กลุ่มยา', a.DACCCODE AS 'รหัสบัญชียา', a.ATCL1CODE AS 'รหัส class ยา 1', a.ATCL2CODE AS 'รหัส class ยา 2', a.ATCL3CODE AS 'รหัส class ยา 3', a.ATCL4CODE AS 'รหัส class ยา 4', a.ATCL5CODE AS 'รหัส class ยา 5', a.ATC AS 'รหัส class ยา ATC', a.DFORM AS 'รหัสรูปแบบยา', a.DGTYID AS 'รหัสประเภทยา', a.INTEN AS 'รหัสความรุนแรง', a.DRGMID AS 'รหัสผู้ผลิต', a.DRGLIST AS 'บัญชียาหลัก', a.GENERICNAME AS 'ชื่อสามัญทางยา', a.TRADENAME AS 'ชื่อทางการค้า', a.DLABEL AS 'ฉลากยา', a.REMARK AS 'หมายเหตุ', a.KEEPID AS 'วิธีการเก็บ', a.LOCATION AS 'สถานที่เก็บยา', a.MAXLV AS 'ปริมาณสูงสุด', a.MINLV AS 'ปริมาณต่ำสุด', a.AVGUSE AS 'อัตราเฉลี่ยการใช้', a.REORDER AS 'ปริมาณถึงจุดสั่งซื้อ', a.`STATUS` AS 'สถานะการเปิดใช้งาน', a.KWORD AS 'คำค้นหา', b.B_UNIT AS 'หน่วยซื้อ', b.S_UNIT AS 'หน่วยขาย'  FROM drugitem a JOIN masproduct b ON a.PRDCODE = b.PRDCODE"

    Property selectDrugitem_ As String
        Get
            Return selectDrugitem
        End Get
        Set(value As String)
            selectDrugitem = value
        End Set
    End Property

    Property DRGCODE_ As String
        Get
            Return DRGCODE
        End Get
        Set(value As String)
            DRGCODE = value
        End Set
    End Property

    Property PRDCODE_ As Integer
        Get
            Return PRDCODE
        End Get
        Set(value As Integer)
            PRDCODE = value
        End Set
    End Property

    Property GDCODE_ As Integer
        Get
            Return GDCODE
        End Get
        Set(value As Integer)
            GDCODE = value
        End Set
    End Property

    Property DACCCODE_ As Integer
        Get
            Return DACCCODE
        End Get
        Set(value As Integer)
            DACCCODE = value
        End Set
    End Property

    Property ATCL1CODE_ As String
        Get
            Return ATCL1CODE
        End Get
        Set(value As String)
            ATCL1CODE = value
        End Set
    End Property

    Property ATCL2CODE_ As String
        Get
            Return ATCL2CODE
        End Get
        Set(value As String)
            ATCL2CODE = value
        End Set
    End Property

    Property ATCL3CODE_ As String
        Get
            Return ATCL3CODE
        End Get
        Set(value As String)
            ATCL3CODE = value
        End Set
    End Property

    Property ATCL4CODE_ As String
        Get
            Return ATCL4CODE
        End Get
        Set(value As String)
            ATCL4CODE = value
        End Set
    End Property

    Property ATCL5CODE_ As String
        Get
            Return ATCL5CODE
        End Get
        Set(value As String)
            ATCL5CODE = value
        End Set
    End Property

    Property ATC_ As String
        Get
            Return ATC
        End Get
        Set(value As String)
            ATC = value
        End Set
    End Property

    Property DFORM_ As Integer
        Get
            Return DFORM
        End Get
        Set(value As Integer)
            DFORM = value
        End Set
    End Property

    Property DGTYID_ As Integer
        Get
            Return DGTYID
        End Get
        Set(value As Integer)
            DGTYID = value
        End Set
    End Property

    Property INTEN_ As Integer
        Get
            Return INTEN
        End Get
        Set(value As Integer)
            INTEN = value
        End Set
    End Property

    Property UGID_ As String
        Get
            Return UGID
        End Get
        Set(value As String)
            UGID = value
        End Set
    End Property

    Property DRGMID_ As Integer
        Get
            Return DRGMID
        End Get
        Set(value As Integer)
            DRGMID = value
        End Set
    End Property

    Property DRGLIST_ As Boolean
        Get
            Return DRGLIST
        End Get
        Set(value As Boolean)
            If value = True Then
                DRGLIST = 1
            Else
                DRGLIST = 0
            End If
        End Set
    End Property

    Property GENERICNAME_ As String
        Get
            Return GENERICNAME
        End Get
        Set(value As String)
            GENERICNAME = value
        End Set
    End Property

    Property TRADENAME_ As String
        Get
            Return TRADENAME
        End Get
        Set(value As String)
            TRADENAME = value
        End Set
    End Property

    Property DLABEL_ As String
        Get
            Return DLABEL
        End Get
        Set(value As String)
            DLABEL = value
        End Set
    End Property

    Property REMARK_ As String
        Get
            Return REMARK
        End Get
        Set(value As String)
            REMARK = value
        End Set
    End Property

    Property KEEPID_ As Integer
        Get
            Return KEEPID
        End Get
        Set(value As Integer)
            KEEPID = value
        End Set
    End Property

    Property LOCATION_ As Integer
        Get
            Return LOCATION
        End Get
        Set(value As Integer)
            LOCATION = value
        End Set
    End Property

    Property MAXLV_ As Double
        Get
            Return MAXLV
        End Get
        Set(value As Double)
            MAXLV = value
        End Set
    End Property

    Property MINLV_ As Double
        Get
            Return MINLV
        End Get
        Set(value As Double)
            MINLV = value
        End Set
    End Property

    Property AVGUSE_ As Double
        Get
            Return AVGUSE
        End Get
        Set(value As Double)
            AVGUSE = value
        End Set
    End Property

    Property REORDER_ As Double
        Get
            Return REORDER
        End Get
        Set(value As Double)
            REORDER = value
        End Set
    End Property

    Property KWORD_ As String
        Get
            Return KWORD
        End Get
        Set(value As String)
            KWORD = value
        End Set
    End Property

    Property STATUS_ As Boolean
        Get
            Return STATUS
        End Get
        Set(value As Boolean)
            If value = True Then
                STATUS = 1
            Else
                STATUS = 0
            End If
        End Set
    End Property

    Property B_UNIT_ As Integer
        Get
            Return B_UNIT
        End Get
        Set(value As Integer)
            B_UNIT = value
        End Set
    End Property

    Property S_UNIT_ As Integer
        Get
            Return S_UNIT
        End Get
        Set(value As Integer)
            S_UNIT = value
        End Set
    End Property

    Property U_UNIT_ As Integer
        Get
            Return U_UNIT
        End Get
        Set(value As Integer)
            U_UNIT = value
        End Set
    End Property

    Property F_MS_ As String
        Get
            Return F_MS
        End Get
        Set(value As String)
            F_MS = value
        End Set
    End Property

    'load combobox (ไม่ใช้แล้ว)
    Public Sub comboboxData(queryCode As String, ByVal comboboxName As ComboBox)
        Dim dataToCombobox As DataTable
        dataToCombobox = connect.GetTable(queryCode)
        Try
            With comboboxName
                .DataSource = dataToCombobox
                .DisplayMember = "b"
                .ValueMember = "a"
                '.BindingContext = BindingContext
            End With
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Public Function addDrugitem() As Boolean
        If connect.GetTable("SELECT drgcode FROM drugitem WHERE drgcode = '" & DRGCODE & "' OR prdcode = '" & PRDCODE & "'").Rows.Count > 0 Then
            MsgBox("มีข้อมูลยานี้อยู่ในระบบแล้ว")
        Else
            Try
                connect = CONDB.NewConnection()
                connect.BeginTrans()

                Dim productCode As Integer

                Dim productSql As String
                productSql = "INSERT INTO masproduct (prdcat, prdname, b_unit, s_unit, u_unit, status) VALUES ('1', '" & GENERICNAME & "', '" & B_UNIT & "', '" & S_UNIT & "', "

                If U_UNIT = 0 Then
                    productSql += "null, "
                Else
                    productSql += "'" & U_UNIT & "', "
                End If

                productSql += "'" & STATUS & "'); SELECT LAST_INSERT_ID();"

                productCode = connect.ExecuteScalar(productSql)

                Dim queryString As String
                queryString = "INSERT INTO drugitem "
                queryString += "(drgcode, prdcode, gdcode, dacccode, atcl1code, atcl2code, atcl3code, atcl4code, atcl5code, atc, dform, dgtyid, inten, ugid, drgmid, drglist, genericname, tradename, dlabel, remark, keepid, location, maxlv, minlv, avguse, reorder, status, kword, f_ms) "
                queryString += "VALUES ("
                queryString += "'" & DRGCODE & "', "
                queryString += "'" & productCode & "', "
                queryString += "'" & GDCODE & "', "
                queryString += "'" & DACCCODE & "', "

                If ATCL1CODE = Nothing Then
                    queryString += "null, "
                Else
                    queryString += "'" & ATCL1CODE & "', "
                End If

                If ATCL2CODE = Nothing Then
                    queryString += "null, "
                Else
                    queryString += "'" & ATCL2CODE & "', "
                End If

                If ATCL3CODE = Nothing Then
                    queryString += "null, "
                Else
                    queryString += "'" & ATCL3CODE & "', "
                End If

                If ATCL4CODE = Nothing Then
                    queryString += "null, "
                Else
                    queryString += "'" & ATCL4CODE & "', "
                End If

                If ATCL5CODE = Nothing Then
                    queryString += "null, "
                Else
                    queryString += "'" & ATCL5CODE & "', "
                End If

                If ATC = Nothing Then
                    queryString += "null, "
                Else
                    queryString += "'" & ATC & "', "
                End If

                queryString += "'" & DFORM & "', "
                queryString += "'" & DGTYID & "', "

                If INTEN = 0 Then
                    queryString += "null, "
                Else
                    queryString += "'" & INTEN & "', "
                End If

                If UGID = Nothing Then
                    queryString += "null, "
                Else
                    queryString += "'" & UGID & "', "
                End If

                If DRGMID = Nothing Then
                    queryString += "null, "
                Else
                    queryString += "'" & DRGMID & "', "
                End If

                queryString += "'" & DRGLIST & "', "
                queryString += "'" & GENERICNAME & "', "
                queryString += "'" & TRADENAME & "', "
                queryString += "'" & DLABEL & "', "
                queryString += "'" & REMARK & "', "

                If KEEPID = Nothing Then
                    queryString += "null, "
                Else
                    queryString += "'" & KEEPID & "', "
                End If

                If LOCATION = Nothing Then
                    queryString += "null, "
                Else
                    queryString += "'" & LOCATION & "', "
                End If

                If MAXLV Is Nothing Then
                    queryString += "null, "
                Else
                    queryString += "'" & MAXLV & "', "
                End If

                If MINLV Is Nothing Then
                    queryString += "null, "
                Else
                    queryString += "'" & MINLV & "', "
                End If

                If AVGUSE Is Nothing Then
                    queryString += "null, "
                Else
                    queryString += "'" & AVGUSE & "', "
                End If

                If REORDER Is Nothing Then
                    queryString += "null, "
                Else
                    queryString += "'" & REORDER & "', "
                End If

                queryString += "'" & STATUS & "', "
                queryString += "'" & KWORD & "', "
                queryString += "'" & F_MS & "')"

                connect.ExecuteNonQuery(queryString)

                'dischargeID = connect.ExecuteScalar("INSERT INTO masdischarge (DISCHARGE, STATUS) VALUES ('" & masdischargeDescription & "', '" & masdischargeStatus & "'); SELECT LAST_INSERT_ID();")
                'connectlocal.ExecuteNonQuery("INSERT INTO MASDISCHARGE (DISCHGID, DISCHARGE, STATUS) VALUES ('" & dischargeID & "', '" & masdischargeDescription & "', '" & masdischargeStatus & "')")
                connect.CommitTrans()
                MsgBox("ท่านได้ทำการเพิ่มข้อมูลยาเรียบร้อยแล้ว")

                Return True
            Catch ex As Exception
                connect.RollbackTrans()
                MsgBox(vbCrLf & ex.Message)
                Return False
            End Try
            connect.Dispose()
            'connectlocal.Dispose()
        End If
    End Function

    Public Function editDrugitem() As Boolean
        Try
            connect = CONDB.NewConnection()
            connect.BeginTrans()

            Dim queryStringDrugitem As String
            queryStringDrugitem = "UPDATE drugitem SET "
            queryStringDrugitem += "gdcode = '" & GDCODE & "', "
            queryStringDrugitem += "dacccode = '" & DACCCODE & "', "

            If ATCL1CODE = Nothing Then
                queryStringDrugitem += "atcl1code = null, "
            Else
                queryStringDrugitem += "atcl1code = '" & ATCL1CODE & "', "
            End If

            If ATCL2CODE = Nothing Then
                queryStringDrugitem += "atcl2code = null, "
            Else
                queryStringDrugitem += "atcl2code = '" & ATCL2CODE & "', "
            End If

            If ATCL3CODE = Nothing Then
                queryStringDrugitem += "atcl3code = null, "
            Else
                queryStringDrugitem += "atcl3code = '" & ATCL3CODE & "', "
            End If

            If ATCL4CODE = Nothing Then
                queryStringDrugitem += "atcl4code = null, "
            Else
                queryStringDrugitem += "atcl4code = '" & ATCL4CODE & "', "
            End If

            If ATCL5CODE = Nothing Then
                queryStringDrugitem += "atcl5code = null, "
            Else
                queryStringDrugitem += "atcl5code = '" & ATCL5CODE & "', "
            End If

            If ATC = Nothing Then
                queryStringDrugitem += "atc = null, "
            Else
                queryStringDrugitem += "atc = '" & ATC & "', "
            End If


            'queryStringDrugitem += "ATCL2CODE = '" & ATCL2CODE & "', "
            'queryStringDrugitem += "ATCL3CODE = '" & ATCL3CODE & "', "
            'queryStringDrugitem += "ATCL4CODE = '" & ATCL4CODE & "', "
            'queryStringDrugitem += "ATCL5CODE = '" & ATCL5CODE & "', "
            'queryStringDrugitem += "ATC = '" & ATC & "', "
            queryStringDrugitem += "dform = '" & DFORM & "', "
            queryStringDrugitem += "dgtyid = '" & DGTYID & "', "


            If INTEN = 0 Then
                queryStringDrugitem += "inten = null, "
            Else
                queryStringDrugitem += "inten = '" & INTEN & "', "
            End If

            If UGID = Nothing Then
                queryStringDrugitem += "ugid = null, "
            Else
                queryStringDrugitem += "ugid = '" & UGID & "', "
            End If

            If DRGMID = Nothing Then
                queryStringDrugitem += "drgmid = null, "
            Else
                queryStringDrugitem += "drgmid = '" & DRGMID & "', "
            End If


            queryStringDrugitem += "drglist = '" & DRGLIST & "', "
            queryStringDrugitem += "genericname = '" & GENERICNAME & "', "
            queryStringDrugitem += "tradename = '" & TRADENAME & "', "
            queryStringDrugitem += "dlabel = '" & DLABEL & "', "
            queryStringDrugitem += "remark = '" & REMARK & "', "


            If KEEPID = Nothing Then
                queryStringDrugitem += "keepid = null, "
            Else
                queryStringDrugitem += "keepid = '" & KEEPID & "', "
            End If

            If LOCATION = Nothing Then
                queryStringDrugitem += "location = null, "
            Else
                queryStringDrugitem += "location = '" & LOCATION & "', "
            End If


            If MAXLV Is Nothing Then
                queryStringDrugitem += "maxlv = null, "
            Else
                queryStringDrugitem += "maxlv = '" & MAXLV & "', "
            End If

            If MINLV Is Nothing Then
                queryStringDrugitem += "minlv = null, "
            Else
                queryStringDrugitem += "minlv = '" & MINLV & "', "
            End If

            If AVGUSE Is Nothing Then
                queryStringDrugitem += "avguse = null, "
            Else
                queryStringDrugitem += "avguse = '" & AVGUSE & "', "
            End If

            If REORDER Is Nothing Then
                queryStringDrugitem += "reorder = null, "
            Else
                queryStringDrugitem += "reorder = '" & REORDER & "', "
            End If

            queryStringDrugitem += "kword = '" & KWORD & "', "
            queryStringDrugitem += "f_ms = '" & F_MS & "', "
            queryStringDrugitem += "status = '" & STATUS & "' "
            queryStringDrugitem += "WHERE drgcode = '" & DRGCODE & "'"
            connect.ExecuteNonQuery(queryStringDrugitem)

            Dim queryStringMasproduct As String
            queryStringMasproduct = "UPDATE masproduct SET "
            queryStringMasproduct += "prdname = '" & GENERICNAME & "', "
            queryStringMasproduct += "b_unit = '" & B_UNIT & "', "
            queryStringMasproduct += "s_unit = '" & S_UNIT & "', "

            If U_UNIT = 0 Then
                queryStringMasproduct += "u_unit = null, "
            Else
                queryStringMasproduct += "u_unit = '" & U_UNIT & "', "
            End If

            queryStringMasproduct += "status = '" & STATUS & "' "
            queryStringMasproduct += "WHERE prdcode = '" & PRDCODE & "'"
            connect.ExecuteNonQuery(queryStringMasproduct)

            'connectlocal.ExecuteNonQuery("UPDATE MASDISCHARGE SET DISCHARGE = '" & masdischargeDescription & "', STATUS = '" & masdischargeStatus & "' WHERE DISCHGID = '" & masdischargeID & "'")

            connect.CommitTrans()
            MsgBox("ท่านได้ทำการแก้ไขข้อมูลยาเรียบร้อยแล้ว")

            Return True
        Catch ex As Exception
            connect.RollbackTrans()
            MsgBox(vbCrLf & ex.Message)
            Return False
        End Try
        connect.Dispose()
        'connectlocal.Dispose()
    End Function

End Class